from datetime import datetime
import pymysql
import datetime
from tools.random_str import *


class PutinDB(object):
    def __init__(self, putin_id):
        self.putin_id = putin_id  # 入库单号
        self.hanjie_order = 1
        self.putin_on = ''
        self.putin_fee = 0
        self.my_id = ''  # 我的元件编号
        self.stock = 0  # 元件当前库存
        self.goods_name = ''  # 元件名称
        self.putin_goods_id = ''  # 商城的元件编号
        self.putin_type = ''  # 元件型号
        self.putin_num = 0  # 入库数量
        self.goods_money = 0  # 元件价格
        self.order = 1  # 序号
        self.brand = ''  # 元件品牌
        self.goods_type = ''  # 元件封装
        self.date = datetime.datetime.now().strftime("%Y-%m-%d")
        self.conn = pymysql.connect('gz-cynosdbmysql-grp-1982fo73.sql.tencentcdb.com', user='root', passwd='Z11714047',
                                    port=25490, db='chip')
        # self.conn = pymysql.connect('127.0.0.1', user='root', passwd='root', db='chip')
        print('创建数据库连接')

    def create_goods_type(self):
        cursor = self.conn.cursor()
        query1 = "INSERT INTO goods_goodspcblib(`name`, `date_joined`) VALUES (%s,%s)"
        query2 = "SELECT id FROM goods_goodspcblib WHERE `name` = %s"
        param = [self.goods_type, self.date]
        cursor.execute(query1, param)
        self.conn.commit()
        cursor.execute(query2, self.goods_type)
        result = cursor.fetchall()
        type_id = result[0][0]
        cursor.close()
        print('封装{}创建成功'.format(self.goods_type))
        return type_id

    def create_goods_brand(self):
        cursor = self.conn.cursor()
        query1 = "INSERT INTO goods_goodsbrand(`name`, `image`, `date_joined`) VALUES (%s,'img/brand/lc.png',%s)"
        query2 = "SELECT id FROM `goods_goodsbrand` WHERE `name` = %s"
        param = [self.brand, self.date]
        cursor.execute(query1, param)
        self.conn.commit()
        cursor.execute(query2, self.brand)
        result = cursor.fetchall()
        brand_id = result[0][0]
        cursor.close()
        print('品牌{}创建成功'.format(self.brand))
        return brand_id

    def create_goods(self):
        cursor = self.conn.cursor()
        query1 = "INSERT INTO goods_goods(`my_id`, `image`, `name`, `stock`, `date_joined`, `category2_id`, `pcb_id`) VALUES (%s,'img/goods/06.jpg',%s, %s,%s, 1, %s)"
        query2 = "SELECT my_id, stock FROM goods_goods WHERE `name` = %s"
        query3 = "SELECT id FROM goods_goodspcblib WHERE `name` = %s"
        cursor.execute(query3, self.goods_type)
        goods_types = cursor.fetchall()
        if goods_types:
            goods_type_id = goods_types[0][0]
        else:
            goods_type_id = self.create_goods_type()
        cursor.execute(query2, self.goods_name)
        result = cursor.fetchall()
        if result:
            self.my_id = result[0][0]
            self.stock = result[0][1]
            print('元件{}已存在，编号:{},库存:{}'.format(self.goods_name, self.my_id, self.stock))
        else:
            self.my_id = my_goods_id()
            param = [self.my_id, self.goods_name, self.putin_num, self.date, goods_type_id]
            cursor.execute(query1, param)
            self.conn.commit()
            print('元件编号{}创建成功'.format(self.my_id))
        cursor.close()

    def create_market_pc(self):
        cursor = self.conn.cursor()
        query = "INSERT INTO goods_goodsmarketid (`market_num`, `date_joined`, `goods_id`, `market_id`) VALUES (%s, %s,%s ,%s)"
        self.create_goods()
        market_id = 1
        param = [self.putin_goods_id, self.date, self.my_id, market_id]
        cursor.execute(query, param)
        self.conn.commit()
        cursor.close()
        print('商城编号{}创建成功'.format(self.putin_goods_id))

    def create_putin(self):
        cursor = self.conn.cursor()
        query1 = "SELECT id FROM goods_goodsmarket WHERE `name` = '立创商城'"
        query2 = "INSERT INTO putin_putinreceipt(receipt_num,fee,market_id,putin_id) VALUES(%s,%s,%s,%s)"
        cursor.execute(query1)
        market = cursor.fetchall()
        market_id = market[0][0]
        param = [self.putin_on, self.putin_fee, market_id, self.putin_id]
        cursor.execute(query2, param)
        self.conn.commit()
        print("送货单{}创建成功".format(self.putin_on))
        cursor.close()

    def create_putin_list(self):
        cursor = self.conn.cursor()
        query1 = "INSERT INTO putin_putinreceiptlist(`order`,number,money,market_num_id,putin_receipt_id,brand_id,brand_num) VALUES(%s,%s,%s,%s,%s,%s,%s)"
        query2 = "SELECT id FROM `goods_goodsbrand` WHERE `name` = %s"
        cursor.execute(query2, self.brand)
        brand = cursor.fetchall()
        if brand:
            brand_id = brand[0][0]
        else:
            brand_id = self.create_goods_brand()
        param = [self.order, self.putin_num, self.goods_money, self.putin_goods_id, self.putin_on, brand_id,
                 self.putin_type]
        cursor.execute(query1, param)
        self.conn.commit()
        cursor.close()

    def get_goods_stock(self):
        cursor = self.conn.cursor()
        query1 = "SELECT goods_id FROM `goods_goodsmarketid` WHERE market_num = %s"
        query2 = "SELECT `name`, stock FROM goods_goods WHERE my_id = %s"
        cursor.execute(query1, self.putin_goods_id)
        result = cursor.fetchall()
        if result:
            self.my_id = result[0][0]
            cursor.execute(query2, self.my_id)
            result1 = cursor.fetchall()
            self.goods_name = result1[0][0]
            self.stock = result1[0][1]
            print('{}现在库存为{}'.format(self.goods_name, self.stock))
        else:
            self.create_market_pc()
        cursor.close()

    def update_stock(self):
        cursor = self.conn.cursor()
        query1 = "SELECT * FROM `putin_putinreceipt` WHERE receipt_num = %s"
        cursor.execute(query1, self.putin_on)
        result = cursor.fetchall()
        if not result:
            self.create_putin()
        self.get_goods_stock()
        self.create_putin_list()
        query = "UPDATE goods_goods SET stock = %s WHERE my_id = %s"
        stock = self.stock + self.putin_num
        param = [stock, self.my_id]
        cursor.execute(query, param)
        self.conn.commit()
        cursor.close()
        print('{}-{}-{}入库成功'.format(self.putin_goods_id, self.goods_name, self.putin_num))

    def down_stock(self, num):
        cursor = self.conn.cursor()
        query = "UPDATE goods_goods SET stock = %s WHERE my_id = %s"
        stock = self.stock - num
        param = [stock, self.my_id]
        cursor.execute(query, param)
        self.conn.commit()
        cursor.close()
        print('{}--{}修改成功,现在库存为{}'.format(self.my_id, self.goods_name, stock))

    def create_hanjielist(self, num, weld_id):
        cursor = self.conn.cursor()
        query = "INSERT INTO `chip`.`weld_weldlist`(`order`, `num`, `goods_id`, `weld_id`) VALUES (%s, %s, %s, %s)"
        order = '{:0>2}'.format(self.hanjie_order)
        param = [order, num, self.my_id, weld_id]
        cursor.execute(query, param)
        self.conn.commit()
        self.hanjie_order += 1
        cursor.close()

    def hanjie(self, weld_id):
        self.get_goods_stock()
        num = eval(input('请输入使用数量：'))
        self.down_stock(num)
        self.create_hanjielist(num, weld_id)

    # def select_places_free(self):
    #     cursor = self.conn.cursor()
    #     query = 'SELECT number FROM places_places WHERE is_free = 1'
    #     cursor.execute(query)
    #     result = cursor.fetchall()
    #     cursor.close()
    #     return result
    #
    # def update_places_notfree(self, place):
    #     cursor = self.conn.cursor()
    #     query = 'UPDATE places_places set is_free=0 WHERE number= %s'
    #     cursor.execute(query, place)
    #     self.conn.commit()
    #     cursor.close()
    #
    # def insert_car(self, number, image, place):
    #     cursor = self.conn.cursor()
    #     query = 'INSERT INTO cars_cars(number,date_joined,image,place_number_id) VALUES(%s,%s,%s,%s)'
    #     time = datetime.now().strftime("%Y-%m-%d %H-%M-%S")
    #     cursor.execute(query, (number, time, image, place))
    #     self.conn.commit()
    #     cursor.close()

    def __del__(self):
        self.conn.close()
        print('删除数据库连接')


if __name__ == '__main__':
    db = PutinDB()
    n = input('输入任意键继续，输入Q退出：')
    while n != 'Q':
        db.create_goods()
        n = input('输入任意键继续，输入Q退出：')
    del db
